Calculation Designer: Cell Overlays

Cell Overlays can be used to manipulate the value of cells with alternative values to achieve a particular analytic outcome. Some classic examples include:

• "What if” scenarios: change values to see how related results would appear
• Custom aggregations: set a parent to the value of the last child element, rather than the sum of its child elements
• Complex calculations: trended values that are the average calculation of all previous elements for a given element, rather than the actual value itself.

Once you click on “Cell Overlay” option in the first Calculation Designer window, you will be presented with this window:

The window displays two tabs:

• Scope: You can select the target section of the data model (or cube) that will be affected by the new formulation.
• Formulation: You can define the custom calculation that will affect the elements specified in the scope.

Designing a Cell Overlay

1. In the Scope tab, you can select the target section of the data model that the cell overlay will affect. The section can comprise:

• Specific elements from some or all hierarchies: If selecting a hard coded element, only 1 element can be selected from a given hierarchy.
• Sets of elements from some or all hierarchies: If using sets, only descendant and level selections can be used to pick a set of elements. Named or Custom Sets cannot be used.

2. Next, you can define the calculation in the Formulation tab. The formulation can be designed around any mathematical equation comprised of data points, numeric functions or other functions. Refer to the explanation for custom members for more detail on the equation editing options.

3. Then, fill in the Metadata panel (at the bottom of the window):

• You can provide a Name and Description for the new Cell Overlay.
• Then, choose the Access permissions. For further information about Security and Access permissions, click here.
• Test: this button allows you to test the new calculation’s syntax (indicated with red arrow above).
  • If the syntax is correct, the response will be, “Query passed!”.
  • If the syntax in incorrect, there will be an error message.
• You can select the checkbox for Cumulative to enable users to multi-select and combine columns and/or rows for query outcomes when using parameters.
• Click OK to finalize the Cell Overlay. The application will automatically display the list of Cell Overlays you have built.